from socket import socket, AF_INET, SOCK_STREAM
import pymssql
import json
import time
import os
import datetime
import sys
import threading


result = os.path.exists('consConfig.json')
# print("配置文件是否存在：", result)
aa = json.loads(open(r'consConfig.json', encoding='utf-8-sig').read())
HOST = aa["jkhost"]
PORT = aa["jkpost"]  # 开设端口
ADDR = (HOST, int(PORT))
title = aa["title"]
version = 'v20211027'
monitor = aa['monitor']
if monitor == -1:
    sys.exit(0)
YD_P1 = aa["YD_P1"]
databasename = '(\'' + aa["database"].replace(',', '\',\'') + '\')'
IS_AutoDeal = aa["IS_AutoDeal"]
Deal_Start = aa["Deal_Start"]
Deal_End = aa["Deal_End"]


# 判断文件夹在不在，不在则新建
def query_exists():
    dest = '.\\result_log'
    if not os.path.exists(dest):
        os.mkdir(dest)


def write_log(logtext):
    curtime = datetime.datetime.strftime(datetime.datetime.today(), '%Y-%m-%d %H:%M:%S')
    date = '.\\result_log\\' + datetime.datetime.strftime(datetime.date.today(), '%Y-%m-%d') + '.txt'
    file_handle = open(date, mode='a', encoding='gbk', errors='ignore')
    file_handle.writelines(curtime + ':' + logtext + '\n')
    file_handle.close()


def del_log():
    d1 = datetime.datetime.now()
    d2 = d1 - datetime.timedelta(days=7)
    delfile = datetime.date.strftime(d2, '%Y-%m-%d')
    L = []
    for root, dirs, files in os.walk('.\\result_log'):
        for file in files:
            if os.path.splitext(file)[1] == '.txt':
                L.append(os.path.join(root, file))
                # print(os.path.splitext(file)[0],delfile)
                if os.path.splitext(file)[0] <= delfile:
                    try:
                        os.remove(os.path.join(root, file))
                        write_log("文件删除完毕—" + os.path.join(root, file))
                    except FileNotFoundError:
                        write_log("文件不存在-" + os.path.join(root, file))


def query_exec(update1):
    try:
        conn = pymssql.connect(server=aa["host"], user=aa["user"], password=aa['password'], database=databasename,
                               charset="GBK" if aa["charset"] == "UTF8" else "GBK", tds_version="7.0")
        cursor = conn.cursor()
        cursor.execute(update1)
        # 确认
        conn.commit()
        # 关闭
        cursor.close()
        conn.close()
        write_log('自动调度执行成功')
    except Exception as e:
        write_log('自动调度执行出错：' + str(e))
        print('自动调度执行出错：' + str(e))
        return query_execbak(update1)


def query_execbak(update1):
    try:
        conn = pymssql.connect(server=aa["host"], user=aa["user"], password=aa['password'], database='master',
                               charset="GBK" if aa["charset"] == "UTF8" else "GBK", tds_version="7.0")
        cursor = conn.cursor()
        cursor.execute(update1)
        # 确认
        conn.commit()
        # 关闭
        cursor.close()
        conn.close()
        write_log('自动调度执行成功')
        print('自动调度执行成功')
    except Exception as e:
        write_log('自动调度执行出错(GBK)：' + str(e))
        print('自动调度执行出错(GBK)：' + str(e))
        return '自动调度执行出错(GBK)：' + str(e)


list = {"0": "头阻塞进程阻塞时间超过30秒(SELECT)，已被终止",
        "1": "头阻塞进程(非查询语句)，暂不处理",
        "2": "头阻塞进程阻塞时间未超过30秒，暂不处理",
        "3": "SELECT执行语句时间超60秒，进程已被终止",
        "4": "执行语句时间小于60秒或非查询语句，暂不处理",
        "5": "人为或系统查询，暂不处理",
        "6": "人为或系统查询，存在头阻塞，请注意",
        "10": "头阻塞进程阻塞时间超过30秒(SELECT)，监控设置不自动处理，请人工判断",
        "11": "头阻塞进程阻塞其它语句未超30秒(SELECT)，请关注",
        "13": "SELECT执行语句时间超60秒，监控设置不自动处理，请人工判断",
        "99": "语句执行已完成但未结束，可能事务未提交！暂不处理。"}


def query_result_YD(SELECT1):
    try:
        conn = pymssql.connect(server=aa["host"], user=aa["user"], password=aa['password'], database='master',
                               charset="GBK", tds_version="7.0")

        conn.autocommit(True)
        cursor = conn.cursor()
        cursor.execute(SELECT1, (databasename, IS_AutoDeal, Deal_Start, Deal_End,))
        result_lock = cursor.fetchall()  # 取出全部数据
        str_lock = ""
        str_issend = ""
        if len(result_lock) > 0:
            str_issend = result_lock[0][-1]
            if len(result_lock) > 5 or str_issend == 1:
                str_lock = "### "+title+"数据库监控 \n #### ***堵塞条数：" + str(len(result_lock)) + "*** \n"
                size = 5 if len(result_lock) > 5 else len(result_lock)
                for i in range(0, size):
                    str_lock += "--- \n> - 使用库：" + str(result_lock[i][-2]) + '\n' + \
                                "> - 使用程序：" + str(result_lock[i][5]) + '\n' + \
                                "> - 登录用户：" + str(result_lock[i][-3]) + '\n' + \
                                "> - 开始时间：" + str(result_lock[i][7]) + '\n' + \
                                "> - 消耗时间：" + str(result_lock[i][-4]) + '\n' + \
                                "> - 执行语句id：" + str(result_lock[i][0]) + '\n' + \
                                "> - 执行语句：" + str(result_lock[i][4]) + '\n' + \
                                "> - 堵塞id：" + str(result_lock[i][3]) + '\n' + \
                                "> - 堵塞语句：" + str(result_lock[i][2]) + '\n' + \
                                "> - 处理备注：" + str(list.get(str(result_lock[i][10]))) + '\n\n'
        # 确认
        conn.commit()
        # 关闭
        cursor.close()
        conn.close()
        write_log(str_lock)
        return str_lock
    except Exception as e:
        write_log('连接数据库出错(GBK)：' + str(e))
        print('连接数据库出错(GBK)：' + str(e))
        return query_result_YDbak(SELECT1)


def query_result_YDbak(SELECT1):
    try:
        conn = pymssql.connect(server=aa["host"], user=aa["user"], password=aa['password'], database=databasename,
                               charset="UTF8", tds_version="7.0")
        conn.autocommit(True)
        cursor = conn.cursor()
        cursor.execute(SELECT1, (databasename, IS_AutoDeal, Deal_Start, Deal_End,))
        result_lock = cursor.fetchall()  # 取出全部数据
        str_lock = ""
        str_issend = ""
        if len(result_lock) > 0:
            str_issend = result_lock[0][-1]
            if len(result_lock) > 5 or str_issend == 1:
                str_lock = "### "+title+"数据库监控 \n #### ***堵塞条数：" + str(len(result_lock)) + "*** \n"
                size = 5 if len(result_lock) > 5 else len(result_lock)
                for i in range(0, size):
                    str_lock += "--- \n > - 使用库：" + str(result_lock[i][-2]) + '\n' + \
                                "> - 使用程序：" + str(result_lock[i][5]) + '\n' + \
                                "> - 登录用户：" + str(result_lock[i][-3]) + '\n' + \
                                "> - 开始时间：" + str(result_lock[i][7]) + '\n' + \
                                "> - 消耗时间：" + str(result_lock[i][-4]) + '\n' + \
                                "> - 执行语句id：" + str(result_lock[i][0]) + '\n' + \
                                "> - 执行语句：" + str(result_lock[i][4]) + '\n' + \
                                "> - 堵塞id：" + str(result_lock[i][3]) + '\n' + \
                                "> - 堵塞语句：" + str(result_lock[i][2]) + '\n' + \
                                "> - 处理备注：" + str(list.get(str(result_lock[i][10]))) + '\n\n'
        # 确认
        conn.commit()
        # 关闭
        cursor.close()
        conn.close()
        write_log(str_lock)
        return str_lock
    except Exception as e:
        write_log('连接数据库出错(UTF8)：' + str(e))
        print('连接数据库出错(UTF8)：' + str(e))
        return '连接数据库出错(UTF8)：' + str(e)


def sendMSG(msg):
    tcpCliSock = socket(AF_INET, SOCK_STREAM)
    tcpCliSock.connect(ADDR)
    send = {"monitor": monitor, "type": "forward", "msg": msg.replace('\'', '^')}
    tcpCliSock.send(str(send).encode())
    tcpCliSock.close()


def toHeart():
    try:
        tcpCliSock = socket(AF_INET, SOCK_STREAM)
        tcpCliSock.connect(ADDR)
        send = {"monitor": monitor, "type": "heart"}
        tcpCliSock.send(str(send).encode())
        tcpCliSock.close()
    except:
        print('toHeart出问题')
    finally:
        threading.Timer(60, toHeart, ()).start()


def doCheck_YD():
    select1 = """use master
    if not exists(select 1 from sysobjects  where  name='t_sqlog')
    begin
        create table t_sqlog
        (
            [id] int identity(1,1)
            ,[Spid] bigint
            , ecid int
            , [Database]  varchar(255)
            , [User]  varchar(255)
            , [Status]  varchar(100)
            ,[Wait_Time(s)] bigint
             ,[Wait] varchar(100)
             , blckSQl nvarchar(max)
            ,blocking_session_id bigint
            , [Inpidual_Query] text
            , Program varchar(255)
            , Hostname varchar(100)
            , nt_domain varchar(100)
            , start_time datetime
            ,[Task_State] varchar(200)
            ,[Command]   varchar(100)
           ,[Wait_Resource] nvarchar(max)
           ,[Blocked_By] bigint
           ,[Head_Blocker] bigint 
           ,[Total_CPU(s)] bigint
           ,[Total_Physical_I/O(MB)]  bigint
           ,[Memory_Use(KB)]  bigint
           , [Open_Transactions] bigint
           ,[Net_Address]   varchar(100)
           , flagid int
           ,curtime datetime not null default getdate()
           ,remark varchar(255)
        )
    end

    create table #table_result
    (	spid bigint,	
        Wait_Time bigint,
        blckSQl varchar(max),
        blocking_session_id	bigint,
        Inpidual_Query	nvarchar(max),
        Program	varchar(255),
        hostname varchar(255),	
        start_time	datetime,
        Head_Blocker int,
        remark	varchar(255),
        ms int,
        [state] int,
        [user] varchar(50),
        databasename varchar(100)
         )

    declare @state int = 0,@remark varchar(255),@DatabaseResult  varchar(100) = %s,@Is_Autodeal varchar(20) = %s, @Deal_Start varchar(5) = %s ,@Deal_End varchar(5) = %s,@issend int = 0
    select IDENTITY(int,1,1) as ID,* 	into #talbe from 
    (
        SELECT 
            distinct [Spid] = s.session_id
            , ecid
            , [Database] = DB_NAME(p.dbid)
            , [User] = p.loginame
            , [Status] = r.status
            ,[Wait_Time(s)]     = ISNULL(p.waittime, 0) / 1000,
            p.waittime,
             [Wait] = ISNULL('w.wait_type', N'')
             ,isnull((select text  from sysprocesses  with (nolock)
                CROSS APPLY sys.dm_exec_sql_text(sql_handle)as qt
                where spid=r.blocking_session_id
            ),'None') as blckSQl
            ,isnull(r.blocking_session_id,'-1') as blocking_session_id
            , [Inpidual_Query] = isnull((select text  from sysprocesses  with (nolock)
                CROSS APPLY sys.dm_exec_sql_text(sql_handle)as qt
                where spid=s.session_id
            ),'None')
            , Program = p.program_name
            , Hostname
            , p.nt_domain
            , isnull(r.start_time,p.last_batch) as start_time
            ,[Task_State]    = ISNULL('t.task_state', N'')
            ,[Command]       = ISNULL(r.command, N'')
           ,[Wait_Resource] = ISNULL('w.resource_description', N'')
           ,[Blocked_By]    = ISNULL(CONVERT (varchar, r.blocking_session_id), '')
           ,[Head_Blocker]  = 
                CASE 
                    WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1' 
                    ELSE '0' END, 
           [Total_CPU(s)] = s.cpu_time/1000
           ,[Total_Physical_I/O(MB)]   = (s.reads + s.writes) * 8 / 1024
           ,[Memory_Use(KB)]  = s.memory_usage * 8192 / 1024
           , [Open_Transactions] = ISNULL(r.open_transaction_count,0)
           ,[Net_Address]   = ISNULL(c.client_net_address, N'') ,
           DATEDIFF(SS,r.start_time,GETDATE()) as ms
        FROM sys.dm_exec_sessions s with(nolock)
        LEFT OUTER JOIN sys.dm_exec_connections c with(nolock) ON (s.session_id = c.session_id)
        LEFT OUTER JOIN sys.dm_exec_requests r with(nolock) ON (s.session_id = r.session_id)
        LEFT OUTER JOIN sys.dm_os_tasks t with(nolock) ON (r.session_id = t.session_id AND r.request_id = t.request_id)
        --OUTER APPLY
		--  (
		--      SELECT TOP 1 wt. wait_type,wt.wait_duration_ms,blocking_session_id
		--      FROM sys.dm_os_waiting_tasks wt with(nolock)
		--      WHERE wt.waiting_task_address = t.task_address
		--      ORDER BY wt.wait_duration_ms desc
		--  ) w
        LEFT OUTER JOIN sys.dm_exec_requests r2 with(nolock) ON (s.session_id = r2.blocking_session_id)
        LEFT OUTER JOIN sys.sysprocesses p with(nolock) ON (s.session_id = p.spid)
        where s.session_id > 50 
        AND s.session_id NOT IN (@@SPID)
        and ISNULL(db_name(p.dbid), N'') in  """+databasename+""" 
        and p.ecid = 0 
    ) k
    where [Head_Blocker]=1 or
    (
        (
        (blocking_session_id>0 and [Wait_Time(s)]>=10)
        or ms>=20 )
        and Program<>'' and Program is not null
        
     )
    ORDER BY [Spid]
    declare @count int
    select @count = COUNT(1) from #talbe
    if(@count>0)
    begin
        declare @flagid int = 1
        select @flagid=isnull(MAX(flagid),0)+1 from t_sqlog with(nolock) 
        insert into t_sqlog(Spid,ecid,[Database],[User],[Status],[Wait_Time(s)],Wait,blckSQl,blocking_session_id,Inpidual_Query,Program,Hostname,nt_domain,start_time,[Task_State],Command,Wait_Resource,Blocked_By,Head_Blocker,[Total_CPU(s)],[Total_Physical_I/O(MB)],[Memory_Use(KB)],[Open_Transactions],Net_Address,flagid)
        select Spid,ecid,[Database],[User],[Status],[Wait_Time(s)],Wait,blckSQl,blocking_session_id,Inpidual_Query,Program,Hostname,nt_domain,start_time,[Task_State],Command,Wait_Resource,Blocked_By,Head_Blocker,[Total_CPU(s)],[Total_Physical_I/O(MB)],[Memory_Use(KB)],[Open_Transactions],Net_Address,@flagid from #talbe
        where Program<>'' or (Program='' and blckSQl<>'None')
         declare 
        @ID1 bigint,@Spid1 bigint,@Wait_Time1 bigint,@blckSQl1 nvarchar(max),
        @blocking_session_id1 bigint,@Inpidual_Query1 nvarchar(max),@Program1 varchar(255),
        @hostname1 varchar(255),@start_time datetime,@Head_Blocker bigint,
        @Command varchar(255),@Program varchar(200),@Database1 varchar(100)
        if exists(select 1 from #talbe where Head_Blocker=1)
        begin
            declare @countM int,
            @bstarime datetime,
            @startime datetime
            select top 1 @Spid1=Spid,@Command=Command,@Program=Program,@Database1=[Database],@startime=start_time from #talbe where Head_Blocker=1 order by Spid
            if(@Program like '%Microsoft SQL Server Management Studio%' or @Program='%Navicat%'  or @Program like '%SQLAgent%' or @Program like '%SqlDbx%' or @Program like '%BCP%')
            begin
                select @state=6,@remark='6'
                update t_sqlog set remark=@remark where Spid=@Spid1 and flagid=@flagid
                insert into #table_result
                select Spid,[Wait_Time(s)],blckSQl,blocking_session_id,Inpidual_Query,Program,
                hostname,start_time,Head_Blocker,@remark as 'remark',[Wait_Time(s)],@state as state,[User],[Database]
                from #talbe where Head_Blocker=1 order by Spid
                
                select @issend = 1
            end
            else
            begin
                if exists(select 1 from #talbe where blocking_session_id=@spid1 and Head_Blocker<>1)
                begin
                    select @countM=isnull(MAX((case when [Wait_Time(s)]>0 then [Wait_Time(s)] else ms end)),0),@bstarime=min(start_time) from #talbe where blocking_session_id=@spid1 and Head_Blocker<>1
                    if(@countM > 30 )
                    begin
                        if(@Command='SELECT')
                        begin
                            if(@Is_Autodeal='Yes' and convert(varchar(5),getdate(),108)>=@Deal_Start and convert(varchar(5),getdate(),108)<=@Deal_End )
                            begin
                                if( @start_time<=@bstarime)
                                begin
                                    exec('kill '+ @Spid1)
                                    update t_sqlog set remark='0kill '+ cast(@Spid1 as varchar(50)) where Spid=@Spid1 and flagid=@flagid
                                    select @state=0,@remark='0'
                                end
                                else
                                begin
                                     update t_sqlog set remark='11' where Spid=@Spid1 and flagid=@flagid
                                     select @state=10,@remark='11'
                                end
                            END
                            else
                            begin
                                update t_sqlog set remark='10' where Spid=@Spid1 and flagid=@flagid
                                select @state=10,@remark='10'
                            end
                            insert into #table_result
                            select Spid,[Wait_Time(s)],blckSQl,blocking_session_id,Inpidual_Query,Program,
                            hostname,start_time,Head_Blocker,@remark as 'remark',@countM,@state as state,[User],[Database]
                            from #talbe where Head_Blocker=1 order by Spid
                        end
                        else
                        begin
                            select @state = 1,@remark='1'
                            update t_sqlog set remark='1' where Spid=@Spid1 and flagid=@flagid
                            insert into #table_result
                            select Spid,[Wait_Time(s)],blckSQl,blocking_session_id,Inpidual_Query,Program,hostname,
                            start_time,Head_Blocker,@remark as 'remark',@countM,@state as state,[User],[Database]
                            from #talbe where Head_Blocker=1 order by Spid
                        end
                        select @issend = 1
                    end
                    else
                    begin
                        select @state = 2,@remark='2',@issend = 1
                        update t_sqlog set remark='2' where Spid=@Spid1 and flagid=@flagid
                        insert into #table_result
                        select Spid as spid,[Wait_Time(s)] as Wait_Time,blckSQl,blocking_session_id,Inpidual_Query,Program,hostname,
                        start_time,Head_Blocker,@remark as 'remark',@countM,@state as state,[User],[Database]
                        from #talbe where Head_Blocker=1 order by Spid
                    end
                end
                ELSE
                BEGIN
				    select @state = 1,@remark='1',@issend = 1
                    update t_sqlog set remark='1_1' where Spid=@Spid1 and flagid=@flagid
                    insert into #table_result
                    select Spid,[Wait_Time(s)],blckSQl,blocking_session_id,Inpidual_Query,Program,hostname,
                    start_time,Head_Blocker,'1_1' as 'remark',@countM,@state as state,[User],[Database]
                    from #talbe where Head_Blocker=1 order by Spid 
                end
            end
            goto sb_exit;
        end
        else
        begin
            sb_exit:
            -----------------------------------------------------------------------------------------------------
            --select * from #talbe
            DECLARE MyCursor CURSOR    
                FOR 
                select [user],[Database],Spid,(case when [Wait_Time(s)]>0 then [Wait_Time(s)] else ms end) as [Wait_Time(s)]
                ,blckSQl,blocking_session_id,Inpidual_Query,Program,hostname,
                start_time,Head_Blocker,Command
                from #talbe WHERE Head_Blocker<>1  
                order by id
            OPEN MyCursor
            declare 
            @Spid2 bigint,@Wait_Time2 bigint,@blckSQl2 nvarchar(max),
            @blocking_session_id2 bigint,@Inpidual_Query2 nvarchar(max),@Program2 varchar(255),
            @hostname2 varchar(255),@start_time2 datetime,@Head_Blocker2 bigint,
            @Command2 varchar(255),@databasename varchar(100),@user varchar(100)
                FETCH NEXT FROM  MyCursor INTO @user,@databasename,@Spid2,@Wait_Time2,@blckSQl2,@blocking_session_id2,@Inpidual_Query2,@Program2,@hostname2,@start_time2,@Head_Blocker2,@Command2
                WHILE @@FETCH_STATUS =0
                BEGIN
                    if(@Program2 like '%Microsoft SQL Server Management Studio%' or @Program2 like '%Navicat%' or @Program2 like '%SQLAgent%' or @Program2 like '%SqlDbx%' or @Program2 like '%BCP%')
                    begin
                        select @remark='5',@state= 5
                        update t_sqlog set remark='5' where Spid=@Spid2 and flagid=@flagid
                            insert into #table_result
                            select  @Spid2 as spid,@Wait_Time2 as Wait_Time,@blckSQl2 as blckSQl,@blocking_session_id2 as blocking_session_id,
                            @Inpidual_Query2 as Inpidual_Query,@Program2 as Program,@hostname2 as hostname,@start_time2 as start_time,
                            @Head_Blocker2 as Head_Blocker,@remark as 'remark',@Wait_Time2,@state as state,@user,@databasename
                        if(@Wait_Time2>=60 or datediff(ss,@start_time2,getdate())>=60)
                        begin
                            select @issend = 1
                        end
                    end
                    else
                    begin
                        if(@Wait_Time2>=60 )
                        begin
                            if(@Command2='SELECT')
                            begin
                                if(@Is_Autodeal='Yes' and convert(varchar(5),getdate(),108)>=@Deal_Start and convert(varchar(5),getdate(),108)<=@Deal_End)
                                begin
                                    exec('kill '+@Spid2)
                                    update t_sqlog set remark='3kill '+ cast(@Spid2 as varchar(50)) where Spid=@Spid2 and flagid=@flagid
                                    select @remark='3',@state= 3
                                end
                                else
                                begin
                                    update t_sqlog set remark='13' where Spid=@Spid2 and flagid=@flagid
                                    select @remark='13',@state= 13
                                end
                                insert into #table_result
                                select  @Spid2 as spid,@Wait_Time2 as Wait_Time,@blckSQl2 as blckSQl,@blocking_session_id2 as blocking_session_id,
                                @Inpidual_Query2 as Inpidual_Query,@Program2 as Program,@hostname2 as hostname,@start_time2 as start_time,
                                @Head_Blocker2 as Head_Blocker,@remark as 'remark',@Wait_Time2,@state as state,@user,@databasename
                            end
                            else
                            begin
                                select @remark='4',@state= 4
                                update t_sqlog set remark='4_1' where Spid=@Spid2 and flagid=@flagid
                                insert into #table_result
                                select  @Spid2 as spid,@Wait_Time2 as Wait_Time,@blckSQl2 as blckSQl,@blocking_session_id2 as blocking_session_id,
                                @Inpidual_Query2 as Inpidual_Query,@Program2 as Program,@hostname2 as hostname,@start_time2 as start_time,
                                @Head_Blocker2 as Head_Blocker,@remark as 'remark',@Wait_Time2,@state as state,@user,@databasename
                            end
                            select @issend = 1
                        end
                        else
                        begin
                            select @remark='4',@state= 4
                            update t_sqlog set remark='4_2' where Spid=@Spid2 and flagid=@flagid
                            insert into #table_result
                            select  @Spid2 as spid,@Wait_Time2 as Wait_Time,@blckSQl2 as blckSQl,@blocking_session_id2 as blocking_session_id,
                            @Inpidual_Query2 as Inpidual_Query,@Program2 as Program,@hostname2 as hostname,@start_time2 as start_time,
                            @Head_Blocker2 as Head_Blocker,@remark as 'remark',@Wait_Time2,@state as state,@user,@databasename
                        end
                    end
                    --update t_sqlog set remark='99' where Spid=@Spid2 and flagid=@flagid and start_time is null
                    FETCH NEXT FROM  MyCursor INTO @user,@databasename,@Spid2,@Wait_Time2,@blckSQl2,@blocking_session_id2,@Inpidual_Query2,@Program2,@hostname2,@start_time2,@Head_Blocker2,@Command2
                END    
            CLOSE MyCursor
            DEALLOCATE MyCursor
        end
    end
    select distinct
    spid,Wait_Time,left(blckSQl,100) as blckSQl,blocking_session_id,left(Inpidual_Query,100) as Inpidual_Query,Program,hostname,start_time,Head_Blocker,remark,state,ms,[user],databasename,@issend as issend  
    from #table_result order by Head_Blocker desc , start_time
    delete from t_sqlog where curtime <=getdate()-30
    drop table #talbe,#table_result"""
    str_result = " 数据库堵塞监控程序运行中，请勿关闭此界面[" + version + "]..."
                 # + " 当前时间:" + datetime.datetime.strftime(datetime.datetime.today(), '%Y-%m-%d %H:%M:%S')
    write_log(str_result)
    print(str_result)
    start_time = time.time()
    msg = query_result_YD(select1)
    end_time = time.time()
    end_start = (datetime.datetime.fromtimestamp(end_time)-datetime.datetime.fromtimestamp(start_time)).seconds
    if end_start>10:
        write_log('### (打印开始)查询堵塞语句执行间隔超过10秒，请注意！执行时间：' + str(end_start) + '秒')
        sendMSG('### 查询堵塞语句执行间隔超过10秒，请注意！执行时间：' + str(end_start) + '秒')
        write_log('### (打印结束)查询堵塞语句执行间隔超过10秒，请注意！执行时间：' + str(end_start) + '秒')
    if str(msg) != "":
        # if datetime.datetime.now().hour >=7 and datetime.datetime.now().hour <= 22:
        sendMSG(msg);
        write_log(msg)
        # print(msg)


def doCheck_AUTO():
    select1 = """use master 
                exec udp_lock
            """
    str_result = " 数据库自动调度程序运行中，请勿关闭此界面[" + version + "]..."
                 # + " 当前时间:" + datetime.datetime.strftime( datetime.datetime.today(), '%Y-%m-%d %H:%M:%S')
    write_log(str_result)
    print(str_result)
    try:
        query_exec(select1)
    except:
        print('自动调度执行出问题')


def docheck_dellog(inc):
    del_log()
    t = threading.Timer(inc, del_log, (inc,))
    t.start()


def open_doCheck_YD():
    try:
        do = threading.Thread(target=doCheck_YD, )
        do.start()
        while do.is_alive():
            time.sleep(0.5)
    except Exception as r:
        write_log(str(r) + "open_doCheck_YD")
    finally:
        threading.Timer(30, open_doCheck_YD, ()).start()


def open_doCheck_AUTO():
    try:
        do = threading.Thread(target=doCheck_AUTO, )
        do.start()
        while do.is_alive():
            time.sleep(0.5)
    except Exception as r:
        write_log(str(r) + "open_doCheck_AUTO")
    finally:
        threading.Timer(30, open_doCheck_AUTO, ()).start()


if __name__ == '__main__':
    query_exists()
    threading.Thread(target=docheck_dellog, args=(3600,)).start()
    if aa["Start_AUTO"] == "Yes":
        open_doCheck_AUTO()
    if aa["Start_YD"] == "Yes":
        open_doCheck_YD()
    try:
        sendMSG("**" + title + "数据库监控自动处理程序启动[" + version + "]...**")
        print("**" + title + "数据库监控自动处理程序启动[" + version + "]...**")
        write_log("**" + "数据库监控自动处理程序启动[" + version + "]...**")
    except Exception as r:
        write_log(str(r))
    toHeart()
